package com.xinzhidi.web.city.controller;

import com.google.common.collect.Lists;
import com.xinzhidi.common.core.domain.entity.SysDictData;
import com.xinzhidi.system.service.ISysDictTypeService;
import com.xinzhidi.web.city.domain.City;
import com.xinzhidi.web.city.service.CityService;
import com.xinzhidi.web.dtuusr.domain.DtuUsr;
import com.xinzhidi.web.dtuusr.service.DtuUsrService;
import io.swagger.annotations.Api;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletResponse;
import java.util.*;
import java.util.stream.Collectors;

@RestController
@RequestMapping("export")
@Slf4j
@Api(tags = "excel下载")
public class ExcelController {

    @Autowired
    private CityService cityService;

    @Autowired
    private DtuUsrService dtuUsrService;

    @Autowired
    private ISysDictTypeService dictTypeService;

    /**
     * 下载excel模板
     *
     * @param response
     * @throws Exception
     */
    @GetMapping(value = "/jilian")
    public void jilian(HttpServletResponse response) throws Exception {
        HSSFWorkbook book = new HSSFWorkbook();
        String[] titleNames = new String[]{"省", "市", "区", "dtu名称", "协议类型", "设备名称", "设备编号", "设备地址", "是否主动抄表", "小区名称", "详细地址", "小区热计量方式", "供热用户", "面积单价", "热量单价", "供热公司", "所属物业", "自动抄表时间间隔", "楼栋名称", "楼栋热计量方式", "单元名称", "楼层数", "单元热计量方式", "住户姓名", "楼层号", "门牌号", "方位", "管道位置", "是否供暖", "采暖面积", "房间热卡号", "固定电话", "移动电话"};
        // 创建需要用户填写的sheet
        HSSFSheet sheetPro = (HSSFSheet) book.createSheet("省市县");
        Row row0 = sheetPro.createRow(0);
        Row row1 = sheetPro.createRow(1);
        CellStyle cellStyle = book.createCellStyle(); // 创建单元格样式对象
        cellStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex()); // 使用颜色索引设置背景颜色

        Cell styleCell;
        for (int i = 0; i < titleNames.length; i++) {
            sheetPro.setColumnWidth(i, 20 * 256); // 设置第一列的宽度为20个字符宽度
            styleCell = row0.createCell(i);
            styleCell.setCellValue(titleNames[i]);
            styleCell.setCellStyle(cellStyle);
        }
        sheetPro.createFreezePane(0,1, 0,1);
        //水平居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        //垂直居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex()); // 设置背景色为黄色

        List<City> List = cityService.list();

        List<DtuUsr> dtuUsrList = dtuUsrService.list();
        List<String> dtuNames = dtuUsrList.stream().map(DtuUsr::getName).collect(Collectors.toList());
        String[] comboBoxs = dtuNames.toArray(new String[]{});


        List<SysDictData> protocolNameData = dictTypeService.selectDictDataByType("protocol_type");
        List<String> protocolNames = protocolNameData.stream().map(SysDictData::getDictLabel).collect(Collectors.toList());
        String[] protocolComboBoxs = protocolNames.toArray(new String[]{});

        List<SysDictData> heatMeterData = dictTypeService.selectDictDataByType("sys_heat_meter");
        List<String> heatMeters = heatMeterData.stream().map(SysDictData::getDictLabel).collect(Collectors.toList());
        String[] heatMeterComboBoxs = heatMeters.toArray(new String[]{});

        List<String> autoReads = Lists.newArrayList("是", "否");
        String[] autoReadsComboBoxs = autoReads.toArray(new String[]{});


        //设置dtu下拉
        this.setSpinnerDataGt256(book, sheetPro, "dtuSheet", 1, 62514, 3, 3, comboBoxs);
        //设置协议类型下拉
        this.setSpinnerDataGt256(book, sheetPro, "PlotProtocolSheet", 1, 62514, 4, 4, protocolComboBoxs);
        //设置是否主动抄表下拉
        this.setSpinnerDataGt256(book, sheetPro, "autoReadSheet", 1, 62514, 8, 8, autoReadsComboBoxs);
        //设置小区热计量方式下拉
        this.setSpinnerDataGt256(book, sheetPro, "PlotHeatMeterSheet", 1, 62514, 11, 11, heatMeterComboBoxs);
        //设置楼栋热计量方式下拉
        this.setSpinnerDataGt256(book, sheetPro, "buildHeatMeterSheet", 1, 62514, 19, 19, heatMeterComboBoxs);
        //设置单元热计量方式下拉
        this.setSpinnerDataGt256(book, sheetPro, "unitHeatMeterSheet", 1, 62514, 22, 22, heatMeterComboBoxs);
        //设置是否供暖下拉
        this.setSpinnerDataGt256(book, sheetPro, "gnSheet", 1, 62514, 28, 28, autoReadsComboBoxs);
        row1.createCell(0).setCellValue("四川省");
        row1.createCell(1).setCellValue("南充市");
        row1.createCell(2).setCellValue("顺庆区");
        row1.createCell(3).setCellValue(dtuNames.get(0));
        row1.createCell(4).setCellValue(protocolNameData.get(0).getDictLabel());
        row1.createCell(5).setCellValue("户用阀门设备1");
        row1.createCell(6).setCellValue("1");
        row1.createCell(7).setCellValue("41580643000001");
        row1.createCell(8).setCellValue("是");
        row1.createCell(9).setCellValue("花间苑");
        row1.createCell(10).setCellValue("四川省南充市顺庆区花间苑");
        row1.createCell(11).setCellValue(heatMeterData.get(0).getDictLabel());
        row1.createCell(12).setCellValue("350");
        row1.createCell(13).setCellValue("131000");
        row1.createCell(14).setCellValue("2.5");
        row1.createCell(15).setCellValue("顺庆供热公司");
        row1.createCell(16).setCellValue("成都物业");
        row1.createCell(17).setCellValue("2");
        row1.createCell(18).setCellValue("1号楼");
        row1.createCell(19).setCellValue(heatMeterData.get(0).getDictLabel());
        row1.createCell(20).setCellValue("1单元");
        row1.createCell(21).setCellValue("31");
        row1.createCell(22).setCellValue(heatMeterData.get(0).getDictLabel());
        row1.createCell(23).setCellValue("xxx");
        row1.createCell(24).setCellValue("7");
        row1.createCell(25).setCellValue("7-1-1001");
        row1.createCell(26).setCellValue("坐北朝南");
        row1.createCell(27).setCellValue("厅及楼梯等部位");
        row1.createCell(28).setCellValue("是");
        row1.createCell(29).setCellValue("90.5");
        row1.createCell(30).setCellValue("12546101");
        row1.createCell(31).setCellValue("2792168");
        row1.createCell(32).setCellValue("15008237107");

        // 查询所有的省名称
        List<City> provinceArr = List.stream().filter(city -> city.getPid() == 0).collect(Collectors.toList());
//        provinceArr.add("安徽省");
//        provinceArr.add("浙江省")
        Map<String, List<String>> areaMap = new HashMap();
        List<City> cityArr = List.stream().filter(city -> city.getId().endsWith("00")).collect(Collectors.toList());

        List<City> areaArr = List.stream().filter(city -> !city.getId().endsWith("00")).collect(Collectors.toList());

        List<String> sitList;
        for (City p : provinceArr) {
            sitList = List.stream().filter(city -> String.valueOf(city.getPid()).equals(p.getId())).map(City::getName).collect(Collectors.toList());
            areaMap.put(p.getName(), sitList);
        }

        for (City c : cityArr) {
            sitList = List.stream().filter(city -> String.valueOf(city.getPid()).equals(c.getId())).map(City::getName).collect(Collectors.toList());
            areaMap.put(c.getName(), sitList);
        }


//        for(String ){
//
//        }

        // 整理数据，放入一个Map中，mapkey存放父地点，value 存放该地点下的子区域


//        areaMap.put("浙江省", Lists.newArrayList("杭州市", "宁波市"));
//        areaMap.put("安徽省", Lists.newArrayList("芜湖市", "滁州市"));
//        areaMap.put("芜湖市", Lists.newArrayList("戈江区", "三山区"));
//        areaMap.put("滁州市", Lists.newArrayList("来安县", "凤阳县"));

        // 创建一个专门用来存放地区信息的隐藏sheet页
        // 因此也不能在现实页之前创建，否则无法隐藏。
        Sheet hideSheet = book.createSheet("area");
        // 这一行作用是将此sheet隐藏，功能未完成时注释此行,可以查看隐藏sheet中信息是否正确
        // book.setSheetHidden(book.getSheetIndex(hideSheet), true);

        int rowId = 0;
        // 设置第一行，存省的信息
        Row provinceRow = hideSheet.createRow(rowId++);
        provinceRow.createCell(0).setCellValue("省列表");
        for (int i = 0; i < provinceArr.size(); i++) {
            Cell provinceCell = provinceRow.createCell(i + 1);
            provinceCell.setCellValue(provinceArr.get(i).getName());
        }
        // 将具体的数据写入到每一行中，行开头为父级区域，后面是子区域。
        Iterator<String> keyIterator = areaMap.keySet().iterator();
        while (keyIterator.hasNext()) {
            String key = keyIterator.next();
            List<String> son = areaMap.get(key);
            Row row = hideSheet.createRow(rowId++);
            row.createCell(0).setCellValue(key);
            for (int i = 0; i < son.size(); i++) {
                Cell cell = row.createCell(i + 1);
                cell.setCellValue(son.get(i));
            }
            // 添加名称管理器
            String range = getRange(1, rowId, son.size());
            Name name = book.createName();
            // key不可重复
            name.setNameName(key);
            String formula = "area!" + range;
            name.setRefersToFormula(formula);
        }

        // 省规则
        DVConstraint provConstraint = DVConstraint.createExplicitListConstraint(provinceArr.stream().map(City::getName).collect(Collectors.toList()).toArray(new String[]{}));
        CellRangeAddressList provRangeAddressList = new CellRangeAddressList(1, 65535, 0, 0);
        DataValidation provinceDataValidation = new HSSFDataValidation(provRangeAddressList, provConstraint);
        provinceDataValidation.createErrorBox("error", "请选择正确的省份");
        sheetPro.addValidationData(provinceDataValidation);
        // 对前20行设置有效性
        for (int i = 2; i < 62514; i++) {
            TemplateExportUtil.setDataValidation(TemplateExportUtil.NumToExcel(0), sheetPro, i, 2);
            TemplateExportUtil.setDataValidation(TemplateExportUtil.NumToExcel(1), sheetPro, i, 3);
        }

        response.setContentType("application/octet-stream");
        response.setHeader("Content-disposition", "attachment;filename=" + "111.xls");
        response.flushBuffer();
        book.write(response.getOutputStream());

    }

    /**
     * 计算formula
     *
     * @param offset   偏移量，如果给0，表示从A列开始，1，就是从B列
     * @param rowId    第几行
     * @param colCount 一共多少列
     * @return 如果给入参 1,1,10. 表示从B1-K1。最终返回 $B$1:$K$1
     */
    public static String getRange(int offset, int rowId, int colCount) {
        char start = (char) ('A' + offset);
        if (colCount <= 25) {
            char end = (char) (start + colCount - 1);
            return "$" + start + "$" + rowId + ":$" + end + "$" + rowId;
        } else {
            char endPrefix = 'A';
            char endSuffix = 'A';
            if ((colCount - 25) / 26 == 0 || colCount == 51) {// 26-51之间，包括边界（仅两次字母表计算）
                if ((colCount - 25) % 26 == 0) {// 边界值
                    endSuffix = (char) ('A' + 25);
                } else {
                    endSuffix = (char) ('A' + (colCount - 25) % 26 - 1);
                }
            } else {// 51以上
                if ((colCount - 25) % 26 == 0) {
                    endSuffix = (char) ('A' + 25);
                    endPrefix = (char) (endPrefix + (colCount - 25) / 26 - 1);
                } else {
                    endSuffix = (char) ('A' + (colCount - 25) % 26 - 1);
                    endPrefix = (char) (endPrefix + (colCount - 25) / 26);
                }
            }
            return "$" + start + "$" + rowId + ":$" + endPrefix + endSuffix + "$" + rowId;
        }
    }

    /**
     * 设置下拉框数据（字符数<=256）
     *
     * @param sheet            sheet页对象
     * @param startRowIndex    开始行索引
     * @param endRowIndex      结束行索引
     * @param startColumnIndex 开始列索引
     * @param endColumnIndex   结束列索引
     * @param spinnerData      下拉框数据
     */
    public static void setSpinnerDataLe256(Sheet sheet, int startRowIndex, int endRowIndex, int startColumnIndex
            , int endColumnIndex, String[] spinnerData) {
        DataValidationHelper helper = sheet.getDataValidationHelper();
        DataValidationConstraint constraint = helper.createExplicitListConstraint(spinnerData);
        //设置单元格范围
        CellRangeAddressList addressList = new CellRangeAddressList(startRowIndex, endRowIndex
                , startColumnIndex, endColumnIndex);
        DataValidation dataValidation = helper.createValidation(constraint, addressList);
        sheet.addValidationData(dataValidation);
    }

    /**
     * 设置下拉框数据（字符数>=256）下拉框数据先写入隐藏Sheet页中，再从隐藏Sheet页读取
     *
     * @param workbook         workbook对象
     * @param sheet            sheet页对象
     * @param hiddenSheetName  隐藏sheet页名称
     * @param startRowIndex    开始行索引
     * @param endRowIndex      结束行索引
     * @param startColumnIndex 开始列索引
     * @param endColumnIndex   结束列索引
     * @param spinnerData      下拉框数据
     */
    public static void setSpinnerDataGt256(Workbook workbook, Sheet sheet, String hiddenSheetName, int startRowIndex, int endRowIndex, int startColumnIndex
            , int endColumnIndex, String[] spinnerData) {
        //设置单元格范围
        CellRangeAddressList addressList = new CellRangeAddressList(startRowIndex, endRowIndex
                , startColumnIndex, endColumnIndex);
        Sheet hiddenSheet = workbook.getSheet(hiddenSheetName);
        if (hiddenSheet == null) {
            hiddenSheet = workbook.createSheet(hiddenSheetName);
            //写入下拉数据到新的sheet页中
            for (int i = 0; i < spinnerData.length; i++) {
                Row row = hiddenSheet.createRow(i);
                Cell cell = row.createCell(0);
                cell.setCellValue(spinnerData[i]);
            }
            //将新建的sheet页隐藏掉
            workbook.setSheetHidden(workbook.getSheetIndex(hiddenSheetName), false);
        }
        DataValidationHelper helper = sheet.getDataValidationHelper();
        //获取隐藏sheet页内容
        String strFormula = hiddenSheetName + "!$A$1:$A$65535";
        DataValidationConstraint constraint = helper.createFormulaListConstraint(strFormula);
        DataValidation dataValidation = helper.createValidation(constraint, addressList);
        sheet.addValidationData(dataValidation);
    }


}
